package com.eastdigit.system.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.eastdigit.system.model.User;
import com.eastdigit.util.Encrypt;
import com.eastdigit.util.PropertiesUtil;
import com.eastdigit.util.StringUtil;

@Service
public class WuhanBusUserService extends BaseService {
	
    /**
     * 同步部门数据
     */
    public synchronized void syncDepts(){
    	JSONArray depts = this.getWuhanDepts();
    	if(depts == null || depts.isEmpty()){
    		return;
    	}
    	
    	StringBuffer sql = new StringBuffer("select * from SYS_DEPARTMENT ");
    	JSONArray dbDepts = this.query(sql.toString());
    	Map<String, JSONObject> dbMaps = new HashMap<String, JSONObject>(); 
    	if(dbDepts != null && !dbDepts.isEmpty()){
    		for(int i = 0 ;i < dbDepts.size(); i++){
        		JSONObject dbDept = dbDepts.getJSONObject(i);
        		dbMaps.put(dbDept.getString("deptId"), dbDept);
        	}
    	}
    	
    	String _sqlUpdate = "update SYS_DEPARTMENT set PARENT_DEPT_ID = ?,DEPT_NAME = ?,SHORT_NAME = ?,FULL_NAME = ? WHERE DEPT_ID = ?";
    	String _sqlInsert = "insert into SYS_DEPARTMENT(DEPT_ID,PARENT_DEPT_ID,DEPT_CODE,DEPT_NAME,SHORT_NAME,FULL_NAME) VALUES(?,?,?,?,?,?)";
    	
    	String deptId, pId,deptName, shortName,fullName;
    	for(int i = 0 ;i < depts.size(); i++){
    		JSONObject _dept= depts.getJSONObject(i);
    		deptId = StringUtil.noNull(_dept.getString("deptId"));
    		pId = StringUtil.noNull(_dept.getString("pId"));
    		deptName = StringUtil.noNull(_dept.getString("deptName"));
    		shortName = StringUtil.noNull(_dept.getString("shortName"));
    		fullName = StringUtil.noNull(_dept.getString("fullName"));
    		
    		JSONObject dbDept = dbMaps.remove(deptId);
    		if(dbDept !=  null){
    			if(!pId.equals(dbDept.getString("parentDeptId"))
    					|| !deptName.equals(dbDept.getString("deptName"))
    					|| !shortName.equals(dbDept.getString("shortName"))
    					|| !fullName.equals(dbDept.getString("fullName"))){//信息变更
    				this.update(_sqlUpdate, pId, deptName,shortName,fullName, deptId);
    			}
    		}else{
    			this.update(_sqlInsert, deptId,pId,deptId, deptName,shortName,fullName);
    		}
    	}
    }
    
    /**
     * 同步用户数据
     */
    public synchronized void syncUsers(){
    	JSONArray users = this.getWuhanUsers();
    	if(users == null || users.isEmpty()){
    		return;
    	}
    	
    	StringBuffer sql = new StringBuffer("select * from SYS_USER ");
    	JSONArray dbUsers = this.query(sql.toString());
    	Map<String, JSONObject> dbMaps = new HashMap<String, JSONObject>(); 
    	if(dbUsers != null && !dbUsers.isEmpty()){
    		for(int i = 0 ;i < dbUsers.size(); i++){
        		JSONObject dbUser = dbUsers.getJSONObject(i);
        		dbMaps.put(dbUser.getString("userId"), dbUser);
        	}
    	}
    	
    	String _sqlUpdate = "update SYS_USER set USER_CODE = ?,USER_NAME = ?,C_USER_STATE = ?,DEPT_ID = ? WHERE USER_ID = ?";
    	String _sqlInsert = "insert into SYS_USER(USER_ID,USER_CODE,USER_NAME,PASSWORD,C_USER_STATE,C_USER_TYPE,DEPT_ID) VALUES(?,?,?,?,?,?,?)";
    	
    	String userId, loginName,userName, state,deptId;
    	for(int i = 0 ;i < users.size(); i++){
    		JSONObject _user= users.getJSONObject(i);
    		userId = StringUtil.noNull(_user.getString("userId"));
    		loginName = StringUtil.noNull(_user.getString("loginName"));
    		userName = StringUtil.noNull(_user.getString("userName"));
    		state = StringUtil.noNull(_user.getString("state"));
    		deptId = StringUtil.noNull(_user.getString("deptId"));
    		state = "0".equals(state) ? "1" : "0";
    		JSONObject dbUser = dbMaps.remove(userId);
    		if(dbUser !=  null){
    			if(!loginName.equals(dbUser.getString("userCode"))
    					|| !userName.equals(dbUser.getString("userName"))
    					|| !state.equals(dbUser.getString("userState"))
    					|| !deptId.equals(dbUser.getString("deptId"))){//信息变更
    				this.update(_sqlUpdate, loginName, userName,state,deptId,userId);
    			}
    		}else{
    			try {
					this.update(_sqlInsert, userId,loginName,userName, Encrypt.encodeMD5(PropertiesUtil.getProperty("project.init.password")),state,User.TYPE_COMMONS,deptId);
				} catch (Exception e) {
					Logger.getLogger(this.getClass()).error(e.getMessage(),e);
				}
    		}
    	}
    }
    
    
    
    
    private static final String WUHANBUS_USER_DB_CONNECTION_DRIVERCLASS = PropertiesUtil.getProperty("wuhanbusUser.db.connection.driverClass");
	private static final String WUHANBUS_USER_DB_CONNECTION_URL = PropertiesUtil.getProperty("wuhanbusUser.db.connection.url");
	private static final String WUHANBUS_USER_DB_CONNECTION_USERNAME = PropertiesUtil.getProperty("wuhanbusUser.db.connection.username");
	private static final String WUHANBUS_USER_DB_CONNECTION_PASSWORD = PropertiesUtil.getProperty("wuhanbusUser.db.connection.password");
	
	private static final String COMP_ID = "D6427056-4235-4939-BEE4-FEC4F2A40ACB";
	
	private static final String USER_QUERY_SQL = "select b.id AS dept_id," +
			" a.ID as user_id," +
			" a.name AS login_name," +
			" a.DisplayName AS user_name," +
			" Cast(a.State as varchar) as state" +
			" FROM UserView a" +
			" LEFT JOIN Department b ON a.MainDepartmentID = b.ID" +
			" WHERE b.IDPath LIKE '%" + COMP_ID + "%'" ;
	
	private static final String DEPARTMENT_QUERY_SQL = "SELECT d.ID AS dept_id," +
			" d.MainDepartmentID AS parent_id," +
			" d.Name AS short_name," +
			" d.DisplayName AS dept_name," +
			" d.DisplayNamePath AS full_name " +
			" FROM DepartmentView d " +
			" WHERE d.IDPath LIKE '%" + COMP_ID + "%'" ;
	
	/**
	 * 获取部门数据
	 * @return
	 */
	public JSONArray getWuhanDepts(){
		JSONArray depts = new JSONArray();
		
		Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
		try {
			Class.forName(WUHANBUS_USER_DB_CONNECTION_DRIVERCLASS);
			conn = DriverManager.getConnection(WUHANBUS_USER_DB_CONNECTION_URL, WUHANBUS_USER_DB_CONNECTION_USERNAME, WUHANBUS_USER_DB_CONNECTION_PASSWORD);
			
			
			pstmt = conn.prepareStatement(DEPARTMENT_QUERY_SQL);
			rs = pstmt.executeQuery();
			
			
			JSONObject dept = null;
			String deptId,pId,shortName,deptName,fullName;
			while (rs.next()) {
				
				deptId = StringUtil.noNull(rs.getString("dept_id"));
				pId = StringUtil.noNull(rs.getString("parent_id"));
				shortName = StringUtil.noNull(rs.getString("short_name"));
				deptName = StringUtil.noNull(rs.getString("dept_name"));
				fullName = StringUtil.noNull(rs.getString("full_name"));
				
				dept = new JSONObject();
				dept.put("deptId", deptId);
				dept.put("pId", pId);
				dept.put("shortName", shortName);
				dept.put("deptName", deptName);
				dept.put("fullName", fullName);
				depts.add(dept);
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			Logger.getLogger(this.getClass()).error(e.getMessage(),e);
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				Logger.getLogger(this.getClass()).error(e.getMessage(),e);
			}
			try {
				pstmt.close();
			} catch (SQLException e) {
				Logger.getLogger(this.getClass()).error(e.getMessage(),e);
			}
			try {
				conn.close();
			} catch (SQLException e) {
				Logger.getLogger(this.getClass()).error(e.getMessage(),e);
			}
		}
		return depts;
	}
 
	/**
	 * 获取用户数据
	 * @return
	 */
	public JSONArray getWuhanUsers(){
		JSONArray users = new JSONArray();
		
		Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
		try {
			Class.forName(WUHANBUS_USER_DB_CONNECTION_DRIVERCLASS);
			conn = DriverManager.getConnection(WUHANBUS_USER_DB_CONNECTION_URL, WUHANBUS_USER_DB_CONNECTION_USERNAME, WUHANBUS_USER_DB_CONNECTION_PASSWORD);
			
			
			pstmt = conn.prepareStatement(USER_QUERY_SQL);
			rs = pstmt.executeQuery();
			
			
			JSONObject user = null;
			String deptId,userId,loginName,userName,state;
			while (rs.next()) {
				
				deptId = StringUtil.noNull(rs.getString("dept_id"));
				userId = StringUtil.noNull(rs.getString("user_id"));
				loginName = StringUtil.noNull(rs.getString("login_name"));
				userName = StringUtil.noNull(rs.getString("user_name"));
				state = StringUtil.noNull(rs.getString("state"));
				
				user = new JSONObject();
				user.put("deptId", deptId);
				user.put("userId", userId);
				user.put("loginName", loginName);
				user.put("userName", userName);
				user.put("state", state);
				users.add(user);
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			Logger.getLogger(this.getClass()).error(e.getMessage(),e);
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				Logger.getLogger(this.getClass()).error(e.getMessage(),e);
			}
			try {
				pstmt.close();
			} catch (SQLException e) {
				Logger.getLogger(this.getClass()).error(e.getMessage(),e);
			}
			try {
				conn.close();
			} catch (SQLException e) {
				Logger.getLogger(this.getClass()).error(e.getMessage(),e);
			}
		}
		return users;
	}
	
	
}
